﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

using SIPO.Library;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.DomainObject.Administration.Catalog;
using SIPO.Library.Enum;
using SIPO.Library.Interfaces;

namespace SIPO.Library.DataAccess.Transaction
{
    public partial class recImpositionRepo : IRepository<recImposition, int>
    {
        public object SelectScalar(SQL.Function.Aggregate function, string column, string parameters)
        {
            object _result = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    switch (function)
                    {
                        case SQL.Function.Aggregate.Max:
                            sbQuery.AppendFormat("SELECT MAX({0}) FROM transactions.recImposition", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Min:
                            sbQuery.AppendFormat("SELECT MIN({0}) FROM transactions.recImposition", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Distinct:
                            sbQuery.AppendFormat("SELECT DISTINCT({0}) FROM transactions.recImposition", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Count:
                            sbQuery.AppendFormat("SELECT COUNT({0}) FROM ", column);
                            sbQuery.AppendFormat("transactions.recImposition ");
                            sbQuery.AppendFormat("inner join transactions.recImpositionDetail on transactions.recImposition.ImpositionID=transactions.recImpositionDetail.ImpositionID ");
                            sbQuery.AppendFormat("inner join master.recImpositionBase on master.recImpositionBase.ImpositionBaseID=transactions.recImposition.ImpositionBaseID ");
                            sbQuery.AppendFormat("inner join master.recDebtorSector on master.recDebtorSector.DebtorSectorID = transactions.recImposition.DebtorSectorID ");
                            sbQuery.AppendFormat("inner join master.recDebtor on master.recDebtor.DebtorID = master.recDebtorSector.DebtorID ");
                            sbQuery.AppendFormat("inner join master.recSubSubSector on master.recSubSubSector.SubSubSectorID=master.recDebtorSector.SubSubSectorID {0}", parameters);
                            break;
                        case SQL.Function.Aggregate.Sum:
                            sbQuery.AppendFormat("SELECT SUM({0}) FROM transactions.recImposition", column, parameters);
                            break;
                        case SQL.Function.Aggregate.Avg:
                            sbQuery.AppendFormat("SELECT AVG({0}) FROM transactions.recImposition", column, parameters);
                            break;
                        default:
                            // do nothing 
                            break;
                    }

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        _result = cmd.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return _result;
        }

        public List<recImposition> GetList(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recImposition> tt = new List<recImposition>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat("      transactions.recImposition.ImpositionID, master.recSubSubSector.SubSubSectorName,master.recDebtor.DebtorName, ");
                    sbQuery.AppendFormat("      master.recImpositionBase.ImpositonBaseName,transactions.recImposition.YearReport, ");
                    sbQuery.AppendFormat("      transactions.recImposition.OJKValue,transactions.recImposition.DebtorValue,transactions.recImposition.TariffValue ");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      transactions.recImposition ");
                    sbQuery.AppendFormat("      inner join transactions.recImpositionDetail on transactions.recImposition.ImpositionID=transactions.recImpositionDetail.ImpositionID ");
                    sbQuery.AppendFormat("      inner join master.recImpositionBase on master.recImpositionBase.ImpositionBaseID=transactions.recImposition.ImpositionBaseID ");
                    sbQuery.AppendFormat("      inner join master.recDebtorSector on master.recDebtorSector.DebtorSectorID = transactions.recImposition.DebtorSectorID ");
                    sbQuery.AppendFormat("      inner join master.recDebtor on master.recDebtor.DebtorID = master.recDebtorSector.DebtorID ");
                    sbQuery.AppendFormat("      inner join master.recSubSubSector on master.recSubSubSector.SubSubSectorID=master.recDebtorSector.SubSubSectorID ");
                    sbQuery.AppendFormat("      {0} ", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recImposition t = new recImposition(true);
                                t.ImpositionID = dr[recImposition.Fields.ImpositionID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.ImpositionID].ToString());
                                t.YearReport = dr[recImposition.Fields.YearReport] == DBNull.Value ? (short)0 : short.Parse(dr[recImposition.Fields.YearReport].ToString());
                                t.DebtorValue = dr[recImposition.Fields.DebtorValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.DebtorValue].ToString());
                                t.OJKValue = dr[recImposition.Fields.OJKValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.OJKValue].ToString());
                                t.TariffValue = dr[recImposition.Fields.TariffValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.TariffValue].ToString());
                                t.recDebtor.DebtorName = dr[recDebtor.Fields.DebtorName] == DBNull.Value ? string.Empty : dr[recDebtor.Fields.DebtorName].ToString();
                                t.recImpositionBase.ImpositonBaseName = dr[recImpositionBase.Fields.ImpositonBaseName] == DBNull.Value ? string.Empty : dr[recImpositionBase.Fields.ImpositonBaseName].ToString();
                                t.recSubSubSector.SubSubSectorName = dr[recSubSubSector.Fields.SubSubSectorName] == DBNull.Value ? string.Empty : dr[recSubSubSector.Fields.SubSubSectorName].ToString();
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public List<recImposition> GetListForCorrectionHistory(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
        {
            List<recImposition> tt = new List<recImposition>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    int startRow = ((currentPage - 1) * pageSize) + 1;
                    int endRow = (startRow + pageSize);

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" WITH result_set AS ");
                    sbQuery.AppendFormat(" ( ");
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat("      ROW_NUMBER() OVER (ORDER BY {0} {1}) AS [row_number], ", sortName, sortOrder);
                    sbQuery.AppendFormat("      transactions.recImpositionDetail.ImpositionDetailID,transactions.recImposition.YearReport, ");
                    sbQuery.AppendFormat("      transactions.recImpositionDetail.OJKValue,transactions.recImpositionDetail.DebtorValue,transactions.recImpositionDetail.TransDate ");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat("      transactions.recImposition ");
                    sbQuery.AppendFormat("      inner join transactions.recImpositionDetail on transactions.recImposition.ImpositionID=transactions.recImpositionDetail.ImpositionID ");
                    sbQuery.AppendFormat("      {0} ", parameters);
                    sbQuery.AppendFormat(" ) ");
                    sbQuery.AppendFormat(" SELECT * FROM result_set WHERE [row_number] BETWEEN {0} AND {1} ", startRow, endRow);

                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recImposition t = new recImposition(true);
                                t.YearReport = dr[recImposition.Fields.YearReport] == DBNull.Value ? (short)0 : short.Parse(dr[recImposition.Fields.YearReport].ToString());
                                t.recImpositionDetail = new recImpositionDetail();
                                t.recImpositionDetail.ImpositionDetailID = dr[recImpositionDetail.Fields.ImpositionDetailID] == DBNull.Value ? 0 : int.Parse(dr[recImpositionDetail.Fields.ImpositionDetailID].ToString());
                                t.recImpositionDetail.DebtorValue = dr[recImpositionDetail.Fields.DebtorValue] == DBNull.Value ? 0 : decimal.Parse(dr[recImpositionDetail.Fields.DebtorValue].ToString());
                                t.recImpositionDetail.OJKValue = dr[recImpositionDetail.Fields.OJKValue] == DBNull.Value ? 0 : decimal.Parse(dr[recImpositionDetail.Fields.OJKValue].ToString());
                                t.recImpositionDetail.TransDate = dr[recImpositionDetail.Fields.TransDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recImpositionDetail.Fields.TransDate].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public bool Save(recImposition domain, recImpositionDetail detail)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strDomain = "INSERT INTO transactions.recImposition(YearReport, DebtorSectorID, DebtorValue, OJKValue, ImpositionBaseID, AttachedFileName, DirectoryLocation, TransTypeID, TariffID, Percentage, DebtorID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, Maximal, Minimal, TariffValue, IsFormula, Attachment, SubSubSectorID) VALUES(@YearReport, @DebtorSectorID, @DebtorValue, @OJKValue, @ImpositionBaseID, @AttachedFileName, @DirectoryLocation, @TransTypeID, @TariffID, @Percentage, @DebtorID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy, @Maximal, @Minimal, @TariffValue, @IsFormula, @Attachment, @SubSubSectorID)";
                    SqlCommand cmd = new SqlCommand(strDomain, conn);
                    cmd.Parameters.Add("@YearReport", SqlDbType.SmallInt).Value = domain.YearReport;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = domain.DebtorSectorID;
                    cmd.Parameters.Add("@DebtorValue", SqlDbType.Money).Value = (object)domain.DebtorValue ?? DBNull.Value;
                    cmd.Parameters.Add("@OJKValue", SqlDbType.Money).Value = (object)domain.OJKValue ?? DBNull.Value;
                    cmd.Parameters.Add("@ImpositionBaseID", SqlDbType.Int).Value = domain.ImpositionBaseID;
                    cmd.Parameters.Add("@AttachedFileName", SqlDbType.VarChar).Value = (object)domain.AttachedFileName ?? DBNull.Value;
                    cmd.Parameters.Add("@DirectoryLocation", SqlDbType.VarChar).Value = (object)domain.DirectoryLocation ?? DBNull.Value;
                    cmd.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = (object)domain.TransTypeID ?? DBNull.Value;
                    cmd.Parameters.Add("@TariffID", SqlDbType.Int).Value = (object)domain.TariffID ?? DBNull.Value;
                    cmd.Parameters.Add("@Percentage", SqlDbType.Real).Value = (object)domain.Percentage ?? DBNull.Value;
                    cmd.Parameters.Add("@DebtorID", SqlDbType.Int).Value = (object)domain.DebtorID ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)domain.CreatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)domain.CreatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@Maximal", SqlDbType.Money).Value = (object)domain.Maximal ?? DBNull.Value;
                    cmd.Parameters.Add("@Minimal", SqlDbType.Money).Value = (object)domain.Minimal ?? DBNull.Value;
                    cmd.Parameters.Add("@TariffValue", SqlDbType.Money).Value = (object)domain.TariffValue ?? DBNull.Value;
                    cmd.Parameters.Add("@IsFormula", SqlDbType.Bit).Value = (object)domain.IsFormula ?? DBNull.Value;
                    cmd.Parameters.Add("@Attachment", SqlDbType.VarBinary).Value = (object)domain.Attachment ?? DBNull.Value;
                    cmd.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = (object)domain.SubSubSectorID ?? DBNull.Value;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); // Save Master
                    cmd.Dispose();

                    string strCommandGetId = " SELECT MAX(ImpositionID) ImpositionID FROM transactions.recImposition WHERE CreatedBy = @CreatedBy ";
                    SqlCommand cmdGetId = new SqlCommand(strCommandGetId, conn);
                    cmdGetId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = domain.CreatedBy;
                    cmdGetId.Transaction = txn;
                    var obj = cmdGetId.ExecuteScalar(); // Get ImpositionID Id 
                    cmdGetId.Dispose();

                    decimal ImpositionId = obj == DBNull.Value ? 0m : Convert.ToDecimal(obj.ToString()); // Get Imposition Id

                    string strDetail = "INSERT INTO transactions.recImpositionDetail(ImpositionID, DebtorValue, OJKValue, IsActive, PrevImpositionDetailID, CalculationID, CreateDate, CreateBy, UpdateDate, UpdateBy, TransDate) VALUES(@ImpositionID, @DebtorValue, @OJKValue, @IsActive, @PrevImpositionDetailID, @CalculationID, @CreateDate, @CreateBy, @UpdateDate, @UpdateBy, @TransDate)";
                    SqlCommand cmdDetail = new SqlCommand(strDetail, conn);
                    cmdDetail.Parameters.Add("@ImpositionID", SqlDbType.Int).Value = Convert.ToInt32(ImpositionId);
                    cmdDetail.Parameters.Add("@DebtorValue", SqlDbType.Money).Value = (object)detail.DebtorValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@OJKValue", SqlDbType.Money).Value = (object)detail.OJKValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)detail.IsActive ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PrevImpositionDetailID", SqlDbType.Bit).Value = (object)detail.PrevImpositionDetailID ?? DBNull.Value;
                    //cmdDetail.Parameters.Add("@IsLastYearAudit", SqlDbType.Bit).Value = (object)detail.IsLastYearAudit ?? DBNull.Value;
                    //cmdDetail.Parameters.Add("@IsCurrentYearAudit", SqlDbType.Bit).Value = (object)detail.IsCurrentYearAudit ?? DBNull.Value;
                    //cmdDetail.Parameters.Add("@IsNoAudit", SqlDbType.Bit).Value = (object)detail.IsNoAudit ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@CalculationID", SqlDbType.Int).Value = (object)detail.CalculationID ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = (object)detail.CreateDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@CreateBy", SqlDbType.VarChar).Value = (object)detail.CreateBy ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = (object)detail.UpdateDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@UpdateBy", SqlDbType.VarChar).Value = (object)detail.UpdateBy ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@TransDate", SqlDbType.DateTime).Value = (object)detail.TransDate ?? DBNull.Value;
                    cmdDetail.Transaction = txn;
                    cmdDetail.ExecuteNonQuery(); // Save Detail
                    cmdDetail.Dispose();

//                    int currentTahunLapKeuYear = domain.YearReport;
//                    int nextYear = DateTime.Now.Year + 1;
//                    for (int i = currentTahunLapKeuYear; i < nextYear; i++)
//                    {
//                        string strCommandCalculationId = @" select trid.CalculationID
//                                                    from transactions.recImposition tri 
//                                                    inner join transactions.recImpositionDetail trid 
//                                                    on trid.ImpositionID=tri.ImpositionID where trid.IsActive=1 
//                                                    and tri.YearReport = @YearReport and tri.DebtorSectorID = @DebtorSectorID and tri.SubSubSectorID = @SubSubSectorID ";
//                        SqlCommand cmdGetCalculationID = new SqlCommand(strCommandCalculationId, conn);
//                        cmdGetCalculationID.CommandType = CommandType.Text;
//                        cmdGetCalculationID.Parameters.Add("@YearReport", SqlDbType.VarChar).Value = i.ToString();
//                        cmdGetCalculationID.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = domain.DebtorSectorID;
//                        cmdGetCalculationID.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = (int)domain.SubSubSectorID;
//                        cmdGetCalculationID.Transaction = txn;
//                        var objCalculationId = cmdGetId.ExecuteScalar(); // Get ImpositionID Id 
//                        cmdGetCalculationID.Dispose();

//                        decimal calculationId = obj == null ? 0m : Convert.ToDecimal(obj.ToString()); // Get Imposition Id

//                        if (calculationId == 0)
//                        {
//                            string strExecuteSp = "[dbo].[SP_GenPeriodicDuesKoreksi]";
//                            SqlCommand cmdExecuteSp = new SqlCommand(strExecuteSp, conn);
//                            cmdExecuteSp.CommandType = CommandType.StoredProcedure;
//                            cmdExecuteSp.Parameters.Add("@pDebtorID", SqlDbType.VarChar).Value = domain.DebtorID;
//                            cmdExecuteSp.Parameters.Add("@Param3", SqlDbType.VarChar).Value = domain.CreatedBy;
//                            cmdExecuteSp.Parameters.Add("@YearRep", SqlDbType.VarChar).Value = i.ToString();
//                            cmdExecuteSp.Parameters.Add("@param2", SqlDbType.VarChar).Value = string.Empty;
//                            cmdExecuteSp.CommandTimeout = 0;
//                            cmdExecuteSp.Transaction = txn;
//                            cmdExecuteSp.ExecuteNonQuery();
//                            cmdExecuteSp.Dispose();
//                        }
//                    }

                    string strExecuteSp = "[dbo].[GenPeriodicDuesLoop]";
                    SqlCommand cmdExecuteSp = new SqlCommand(strExecuteSp, conn);
                    cmdExecuteSp.CommandType = CommandType.StoredProcedure;
                    cmdExecuteSp.Parameters.Add("@pDebtorID", SqlDbType.VarChar).Value = domain.DebtorID;
                    cmdExecuteSp.Parameters.Add("@Param3", SqlDbType.VarChar).Value = domain.CreatedBy;
                    cmdExecuteSp.Parameters.Add("@YearRep", SqlDbType.VarChar).Value = domain.YearReport;
                    cmdExecuteSp.Parameters.Add("@param2", SqlDbType.VarChar).Value = string.Empty;
                    cmdExecuteSp.CommandTimeout = 0;
                    cmdExecuteSp.Transaction = txn;
                    cmdExecuteSp.ExecuteNonQuery();
                    cmdExecuteSp.Dispose();

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }

            }
            return result;
        }

        public bool SaveCorrection(recImposition domain, recImpositionDetail detail)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strDomain = "UPDATE transactions.recImposition SET DebtorValue = @DebtorValue, OJKValue = @OJKValue, ";
                    strDomain += " UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy, AttachedFileName = @AttachedFileName, Attachment = @Attachment WHERE ImpositionID = @ImpositionID AND YearReport = @YearReport AND DebtorSectorID = @DebtorSectorID AND SubSubSectorID = @SubSubSectorID ";
                    SqlCommand cmd = new SqlCommand(strDomain, conn);
                    cmd.Parameters.Add("@ImpositionID", SqlDbType.Int).Value = domain.ImpositionID;
                    cmd.Parameters.Add("@YearReport", SqlDbType.SmallInt).Value = domain.YearReport;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = domain.DebtorSectorID;
                    cmd.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = (object)domain.SubSubSectorID ?? DBNull.Value;
                    cmd.Parameters.Add("@DebtorValue", SqlDbType.Money).Value = (object)domain.DebtorValue ?? DBNull.Value;
                    cmd.Parameters.Add("@OJKValue", SqlDbType.Money).Value = (object)domain.OJKValue ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@AttachedFileName", SqlDbType.VarChar).Value = (object)domain.AttachedFileName ?? DBNull.Value;
                    cmd.Parameters.Add("@Attachment", SqlDbType.VarBinary).Value = (object)domain.Attachment ?? DBNull.Value;
                    
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); // Update Master
                    cmd.Dispose();

                    string strDetailUpdate = "UPDATE transactions.recImpositionDetail SET IsActive = 0, UpdateDate = @UpdateDate, UpdateBy = @UpdateBy WHERE ImpositionDetailID = @ImpositionDetailID AND ImpositionID = @ImpositionID AND IsActive=1";
                    SqlCommand cmdDetailUpdate = new SqlCommand(strDetailUpdate, conn);
                    cmdDetailUpdate.Parameters.Add("@ImpositionDetailID", SqlDbType.Int).Value = detail.ImpositionDetailID;
                    cmdDetailUpdate.Parameters.Add("@ImpositionID", SqlDbType.Int).Value = domain.ImpositionID;
                    cmdDetailUpdate.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = (object)detail.UpdateDate ?? DBNull.Value;
                    cmdDetailUpdate.Parameters.Add("@UpdateBy", SqlDbType.VarChar).Value = (object)detail.UpdateBy ?? DBNull.Value;
                    cmdDetailUpdate.Transaction = txn;
                    cmdDetailUpdate.ExecuteNonQuery(); // set isActive=0
                    cmdDetailUpdate.Dispose();

                    string strDetail = "INSERT INTO transactions.recImpositionDetail(ImpositionID, DebtorValue, OJKValue, IsActive, PrevImpositionDetailID, CalculationID, CreateDate, CreateBy, UpdateDate, UpdateBy, TransDate) VALUES(@ImpositionID, @DebtorValue, @OJKValue, @IsActive, @PrevImpositionDetailID, @CalculationID, @CreateDate, @CreateBy, @UpdateDate, @UpdateBy, @TransDate)";
                    SqlCommand cmdDetail = new SqlCommand(strDetail, conn);
                    cmdDetail.Parameters.Add("@ImpositionID", SqlDbType.Int).Value = domain.ImpositionID;
                    cmdDetail.Parameters.Add("@DebtorValue", SqlDbType.Money).Value = (object)detail.DebtorValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@OJKValue", SqlDbType.Money).Value = (object)detail.OJKValue ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)detail.IsActive ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@PrevImpositionDetailID", SqlDbType.Int).Value = (object)detail.ImpositionDetailID ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@CalculationID", SqlDbType.Int).Value = (object)detail.CalculationID ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = (object)detail.CreateDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@CreateBy", SqlDbType.VarChar).Value = (object)detail.CreateBy ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = (object)detail.UpdateDate ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@UpdateBy", SqlDbType.VarChar).Value = (object)detail.UpdateBy ?? DBNull.Value;
                    cmdDetail.Parameters.Add("@TransDate", SqlDbType.DateTime).Value = (object)detail.TransDate ?? DBNull.Value;
                    cmdDetail.Transaction = txn;
                    cmdDetail.ExecuteNonQuery(); // Save Detail
                    cmdDetail.Dispose();

//                    int currentTahunLapKeuYear = domain.YearReport;
//                    int nextYear = DateTime.Now.Year + 1;
//                    for (int i = currentTahunLapKeuYear; i < nextYear; i++)
//                    {
//                        string strCommandGetId = @" select trid.CalculationID
//                                                    from transactions.recImposition tri 
//                                                    inner join transactions.recImpositionDetail trid 
//                                                    on trid.ImpositionID=tri.ImpositionID where trid.IsActive=1 
//                                                    and tri.YearReport = @YearReport and tri.DebtorSectorID = @DebtorSectorID and tri.SubSubSectorID = @SubSubSectorID ";
//                        SqlCommand cmdGetId = new SqlCommand(strCommandGetId, conn);
//                        cmdGetId.CommandType = CommandType.Text;
//                        cmdGetId.Parameters.Add("@YearReport", SqlDbType.VarChar).Value = i.ToString();
//                        cmdGetId.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = domain.DebtorSectorID;
//                        cmdGetId.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = (int)domain.SubSubSectorID;
//                        cmdGetId.Transaction = txn;
//                        var obj = cmdGetId.ExecuteScalar(); // Get ImpositionID Id 
//                        cmdGetId.Dispose();

//                        decimal calculationId = obj == null ? 0m : Convert.ToDecimal(obj.ToString()); // Get Imposition Id

//                        if (calculationId == 0)
//                        {
//                            string strExecuteSp = "[dbo].[SP_GenPeriodicDuesKoreksi]";
//                            SqlCommand cmdExecuteSp = new SqlCommand(strExecuteSp, conn);
//                            cmdExecuteSp.CommandType = CommandType.StoredProcedure;
//                            cmdExecuteSp.Parameters.Add("@pDebtorID", SqlDbType.VarChar).Value = domain.DebtorID;
//                            cmdExecuteSp.Parameters.Add("@Param3", SqlDbType.VarChar).Value = domain.CreatedBy;
//                            cmdExecuteSp.Parameters.Add("@YearRep", SqlDbType.VarChar).Value = i.ToString();
//                            cmdExecuteSp.Parameters.Add("@param2", SqlDbType.VarChar).Value = string.Empty;
//                            cmdExecuteSp.CommandTimeout = 0;
//                            cmdExecuteSp.Transaction = txn;
//                            cmdExecuteSp.ExecuteNonQuery();
//                            cmdExecuteSp.Dispose();
//                        }
//                    }
                    string strExecuteSp = "[dbo].[GenPeriodicDuesLoop]";
                    SqlCommand cmdExecuteSp = new SqlCommand(strExecuteSp, conn);
                    cmdExecuteSp.CommandType = CommandType.StoredProcedure;
                    cmdExecuteSp.Parameters.Add("@pDebtorID", SqlDbType.VarChar).Value = domain.DebtorID;
                    cmdExecuteSp.Parameters.Add("@Param3", SqlDbType.VarChar).Value = domain.CreatedBy;
                    cmdExecuteSp.Parameters.Add("@YearRep", SqlDbType.VarChar).Value = domain.YearReport;
                    cmdExecuteSp.Parameters.Add("@param2", SqlDbType.VarChar).Value = string.Empty;
                    cmdExecuteSp.CommandTimeout = 0;
                    cmdExecuteSp.Transaction = txn;
                    cmdExecuteSp.ExecuteNonQuery();
                    cmdExecuteSp.Dispose();

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }

            }
            return result;
        }

        public bool Save(List<recImposition> master, List<recImpositionDetail> detail)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    int i = 0;
                    foreach (recImposition domain in master)
                    {
                        //recImposition = this.GetById(domain.ImpositionID, domain.YearReport.ToString(), domain.DebtorSectorID, (int)domain.SubSubSectorID);
                        SqlCommand cmdGetImposition = conn.CreateCommand();                        
                        recImposition recImposition = null;

                        StringBuilder sbQuery = new StringBuilder();
                        sbQuery.AppendFormat(" select tri.YearReport,tri.ImpositionID,trid.CalculationID,tri.DebtorID,tri.DebtorSectorID,tri.SubSubSectorID,trid.ImpositionDetailID ");
                        sbQuery.AppendFormat(" from transactions.recImposition tri ");
                        sbQuery.AppendFormat(" inner join transactions.recImpositionDetail trid ");
                        sbQuery.AppendFormat(" on trid.ImpositionID=tri.ImpositionID where tri.ImpositionID = @ImpositionID and trid.IsActive=1 ");
                        sbQuery.AppendFormat(" and tri.YearReport = @YearReport and tri.DebtorSectorID = @DebtorSectorID and tri.SubSubSectorID = @SubSubSectorID ");
                        cmdGetImposition.CommandText = sbQuery.ToString();
                        cmdGetImposition.CommandType = CommandType.Text;
                        cmdGetImposition.Parameters.Add("@ImpositionID", SqlDbType.Int).Value = domain.ImpositionID;
                        cmdGetImposition.Parameters.Add("@YearReport", SqlDbType.VarChar).Value = domain.YearReport.ToString();
                        cmdGetImposition.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = domain.DebtorSectorID;
                        cmdGetImposition.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = (int)domain.SubSubSectorID;
                        cmdGetImposition.Transaction = txn;
                        using (SqlDataReader dr = cmdGetImposition.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                recImposition = new recImposition();
                                recImposition.ImpositionID = dr[recImposition.Fields.ImpositionID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.ImpositionID].ToString());
                                recImposition.YearReport = dr[recImposition.Fields.YearReport] == DBNull.Value ? (short)0 : short.Parse(dr[recImposition.Fields.YearReport].ToString());
                                recImposition.DebtorSectorID = dr[recImposition.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.DebtorSectorID].ToString());
                                recImposition.DebtorID = dr[recImposition.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.DebtorID].ToString());
                                recImposition.SubSubSectorID = dr[recImposition.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.SubSubSectorID].ToString());
                                recImposition.recImpositionDetail = new recImpositionDetail();
                                recImposition.recImpositionDetail.CalculationID = dr[recImpositionDetail.Fields.CalculationID] == DBNull.Value ? 0 : int.Parse(dr[recImpositionDetail.Fields.CalculationID].ToString());
                                recImposition.recImpositionDetail.ImpositionDetailID = dr[recImpositionDetail.Fields.ImpositionDetailID] == DBNull.Value ? 0 : int.Parse(dr[recImpositionDetail.Fields.ImpositionDetailID].ToString());
                            }
                        }
                        cmdGetImposition.Dispose();

                        if (recImposition != null)
                        {
                            domain.ImpositionID = recImposition.ImpositionID;
                            domain.UpdatedBy = domain.CreatedBy;
                            domain.UpdatedDate = DateTime.Now;

                            detail[i].ImpositionDetailID = recImposition.recImpositionDetail.ImpositionDetailID;
                            detail[i].UpdateBy = domain.CreatedBy;
                            detail[i].UpdateDate = DateTime.Now;
                            if (recImposition.recImpositionDetail.CalculationID != 0)
                            {
                                //koreksi
                                detail[i].CalculationID = recImposition.recImpositionDetail.CalculationID;
                                // update imposition, update impositiondetail isactive = 0 ,save impositiondetail, run [dbo].[SP_GenPeriodicDues]
                                string strDomain = "UPDATE transactions.recImposition SET DebtorValue = @DebtorValue, OJKValue = @OJKValue, ";
                                strDomain += " UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE ImpositionID = @ImpositionID AND YearReport = @YearReport AND DebtorSectorID = @DebtorSectorID AND SubSubSectorID = @SubSubSectorID ";
                                SqlCommand cmd = new SqlCommand(strDomain, conn);
                                cmd.Parameters.Add("@ImpositionID", SqlDbType.Int).Value = domain.ImpositionID;
                                cmd.Parameters.Add("@YearReport", SqlDbType.SmallInt).Value = domain.YearReport;
                                cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = domain.DebtorSectorID;
                                cmd.Parameters.Add("@DebtorValue", SqlDbType.Money).Value = (object)domain.DebtorValue ?? DBNull.Value;
                                cmd.Parameters.Add("@OJKValue", SqlDbType.Money).Value = (object)domain.OJKValue ?? DBNull.Value;
                                cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                                cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                                cmd.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = (object)domain.SubSubSectorID ?? DBNull.Value;
                                cmd.Transaction = txn;
                                cmd.ExecuteNonQuery(); // Update Master
                                cmd.Dispose();

                                string strDetailUpdate = "UPDATE transactions.recImpositionDetail SET IsActive = 0, UpdateDate = @UpdateDate, UpdateBy = @UpdateBy WHERE ImpositionDetailID = @ImpositionDetailID AND ImpositionID = @ImpositionID AND IsActive=1";
                                SqlCommand cmdDetailUpdate = new SqlCommand(strDetailUpdate, conn);
                                cmdDetailUpdate.Parameters.Add("@ImpositionDetailID", SqlDbType.Int).Value = detail[i].ImpositionDetailID;
                                cmdDetailUpdate.Parameters.Add("@ImpositionID", SqlDbType.Int).Value = domain.ImpositionID;
                                cmdDetailUpdate.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = (object)detail[i].UpdateDate ?? DBNull.Value;
                                cmdDetailUpdate.Parameters.Add("@UpdateBy", SqlDbType.VarChar).Value = (object)detail[i].UpdateBy ?? DBNull.Value;
                                cmdDetailUpdate.Transaction = txn;
                                cmdDetailUpdate.ExecuteNonQuery(); // set isActive=0
                                cmdDetailUpdate.Dispose();

                                string strDetail = "INSERT INTO transactions.recImpositionDetail(ImpositionID, DebtorValue, OJKValue, IsActive, PrevImpositionDetailID, CalculationID, CreateDate, CreateBy, UpdateDate, UpdateBy, TransDate) VALUES(@ImpositionID, @DebtorValue, @OJKValue, @IsActive, @PrevImpositionDetailID, @CalculationID, @CreateDate, @CreateBy, @UpdateDate, @UpdateBy, @TransDate)";
                                SqlCommand cmdDetail = new SqlCommand(strDetail, conn);
                                cmdDetail.Parameters.Add("@ImpositionID", SqlDbType.Int).Value = domain.ImpositionID;
                                cmdDetail.Parameters.Add("@DebtorValue", SqlDbType.Money).Value = (object)detail[i].DebtorValue ?? DBNull.Value;
                                cmdDetail.Parameters.Add("@OJKValue", SqlDbType.Money).Value = (object)detail[i].OJKValue ?? DBNull.Value;
                                cmdDetail.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)detail[i].IsActive ?? DBNull.Value;
                                cmdDetail.Parameters.Add("@PrevImpositionDetailID", SqlDbType.Int).Value = (object)detail[i].ImpositionDetailID ?? DBNull.Value;
                                cmdDetail.Parameters.Add("@CalculationID", SqlDbType.Int).Value = (object)detail[i].CalculationID ?? DBNull.Value;
                                cmdDetail.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = (object)detail[i].CreateDate ?? DBNull.Value;
                                cmdDetail.Parameters.Add("@CreateBy", SqlDbType.VarChar).Value = (object)detail[i].CreateBy ?? DBNull.Value;
                                cmdDetail.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = (object)detail[i].UpdateDate ?? DBNull.Value;
                                cmdDetail.Parameters.Add("@UpdateBy", SqlDbType.VarChar).Value = (object)detail[i].UpdateBy ?? DBNull.Value;
                                cmdDetail.Parameters.Add("@TransDate", SqlDbType.DateTime).Value = (object)detail[i].TransDate ?? DBNull.Value;
                                cmdDetail.Transaction = txn;
                                cmdDetail.ExecuteNonQuery(); // Save Detail
                                cmdDetail.Dispose();

                                string strExecuteSp = "[dbo].[SP_GenPeriodicDuesKoreksi]";
                                SqlCommand cmdExecuteSp = new SqlCommand(strExecuteSp, conn);
                                cmdExecuteSp.CommandType = CommandType.StoredProcedure;
                                cmdExecuteSp.Parameters.Add("@pDebtorID", SqlDbType.VarChar).Value = domain.DebtorID;
                                cmdExecuteSp.Parameters.Add("@Param3", SqlDbType.VarChar).Value = domain.CreatedBy;
                                cmdExecuteSp.Parameters.Add("@YearRep", SqlDbType.VarChar).Value = domain.YearReport;
                                cmdExecuteSp.Parameters.Add("@param2", SqlDbType.VarChar).Value = string.Empty;
                                cmdExecuteSp.CommandTimeout = 0;
                                cmdExecuteSp.Transaction = txn;
                                cmdExecuteSp.ExecuteNonQuery();
                                cmdExecuteSp.Dispose();
                            }
                        }
                        else
                        {
                            // save imposition,impositiondetail, run [dbo].[SP_GenPeriodicDues]
                            string strDomain = "INSERT INTO transactions.recImposition(YearReport, DebtorSectorID, DebtorValue, OJKValue, ImpositionBaseID, AttachedFileName, DirectoryLocation, TransTypeID, TariffID, Percentage, DebtorID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, Maximal, Minimal, TariffValue, IsFormula, Attachment, SubSubSectorID) VALUES(@YearReport, @DebtorSectorID, @DebtorValue, @OJKValue, @ImpositionBaseID, @AttachedFileName, @DirectoryLocation, @TransTypeID, @TariffID, @Percentage, @DebtorID, @CreatedDate, @CreatedBy, @UpdatedDate, @UpdatedBy, @Maximal, @Minimal, @TariffValue, @IsFormula, @Attachment, @SubSubSectorID)";
                            SqlCommand cmd = new SqlCommand(strDomain, conn);
                            cmd.Parameters.Add("@YearReport", SqlDbType.SmallInt).Value = domain.YearReport;
                            cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = domain.DebtorSectorID;
                            cmd.Parameters.Add("@DebtorValue", SqlDbType.Money).Value = (object)domain.DebtorValue ?? DBNull.Value;
                            cmd.Parameters.Add("@OJKValue", SqlDbType.Money).Value = (object)domain.OJKValue ?? DBNull.Value;
                            cmd.Parameters.Add("@ImpositionBaseID", SqlDbType.Int).Value = domain.ImpositionBaseID;
                            cmd.Parameters.Add("@AttachedFileName", SqlDbType.VarChar).Value = (object)domain.AttachedFileName ?? DBNull.Value;
                            cmd.Parameters.Add("@DirectoryLocation", SqlDbType.VarChar).Value = (object)domain.DirectoryLocation ?? DBNull.Value;
                            cmd.Parameters.Add("@TransTypeID", SqlDbType.SmallInt).Value = (object)domain.TransTypeID ?? DBNull.Value;
                            cmd.Parameters.Add("@TariffID", SqlDbType.Int).Value = (object)domain.TariffID ?? DBNull.Value;
                            cmd.Parameters.Add("@Percentage", SqlDbType.Real).Value = (object)domain.Percentage ?? DBNull.Value;
                            cmd.Parameters.Add("@DebtorID", SqlDbType.Int).Value = (object)domain.DebtorID ?? DBNull.Value;
                            cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = (object)domain.CreatedDate ?? DBNull.Value;
                            cmd.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = (object)domain.CreatedBy ?? DBNull.Value;
                            cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                            cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                            cmd.Parameters.Add("@Maximal", SqlDbType.Money).Value = (object)domain.Maximal ?? DBNull.Value;
                            cmd.Parameters.Add("@Minimal", SqlDbType.Money).Value = (object)domain.Minimal ?? DBNull.Value;
                            cmd.Parameters.Add("@TariffValue", SqlDbType.Money).Value = (object)domain.TariffValue ?? DBNull.Value;
                            cmd.Parameters.Add("@IsFormula", SqlDbType.Bit).Value = (object)domain.IsFormula ?? DBNull.Value;
                            cmd.Parameters.Add("@Attachment", SqlDbType.VarBinary).Value = (object)domain.Attachment ?? DBNull.Value;
                            cmd.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = (object)domain.SubSubSectorID ?? DBNull.Value;
                            cmd.Transaction = txn;
                            cmd.ExecuteNonQuery(); // Save Master
                            cmd.Dispose();

                            string strCommandGetId = " SELECT MAX(ImpositionID) ImpositionID FROM transactions.recImposition WHERE CreatedBy = @CreatedBy ";
                            SqlCommand cmdGetId = new SqlCommand(strCommandGetId, conn);
                            cmdGetId.Parameters.Add("@CreatedBy", SqlDbType.VarChar).Value = domain.CreatedBy;
                            cmdGetId.Transaction = txn;
                            var obj = cmdGetId.ExecuteScalar(); // Get ImpositionID Id 
                            cmdGetId.Dispose();

                            decimal ImpositionId = obj == DBNull.Value ? 0m : Convert.ToDecimal(obj.ToString()); // Get Imposition Id

                            string strDetail = "INSERT INTO transactions.recImpositionDetail(ImpositionID, DebtorValue, OJKValue, IsActive, PrevImpositionDetailID, CalculationID, CreateDate, CreateBy, UpdateDate, UpdateBy, TransDate) VALUES(@ImpositionID, @DebtorValue, @OJKValue, @IsActive, @PrevImpositionDetailID, @CalculationID, @CreateDate, @CreateBy, @UpdateDate, @UpdateBy, @TransDate)";
                            SqlCommand cmdDetail = new SqlCommand(strDetail, conn);
                            cmdDetail.Parameters.Add("@ImpositionID", SqlDbType.Int).Value = Convert.ToInt32(ImpositionId);
                            cmdDetail.Parameters.Add("@DebtorValue", SqlDbType.Money).Value = (object)detail[i].DebtorValue ?? DBNull.Value;
                            cmdDetail.Parameters.Add("@OJKValue", SqlDbType.Money).Value = (object)detail[i].OJKValue ?? DBNull.Value;
                            cmdDetail.Parameters.Add("@IsActive", SqlDbType.Bit).Value = (object)detail[i].IsActive ?? DBNull.Value;
                            cmdDetail.Parameters.Add("@PrevImpositionDetailID", SqlDbType.Bit).Value = (object)detail[i].PrevImpositionDetailID ?? DBNull.Value;
                            cmdDetail.Parameters.Add("@CalculationID", SqlDbType.Int).Value = (object)detail[i].CalculationID ?? DBNull.Value;
                            cmdDetail.Parameters.Add("@CreateDate", SqlDbType.DateTime).Value = (object)detail[i].CreateDate ?? DBNull.Value;
                            cmdDetail.Parameters.Add("@CreateBy", SqlDbType.VarChar).Value = (object)detail[i].CreateBy ?? DBNull.Value;
                            cmdDetail.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = (object)detail[i].UpdateDate ?? DBNull.Value;
                            cmdDetail.Parameters.Add("@UpdateBy", SqlDbType.VarChar).Value = (object)detail[i].UpdateBy ?? DBNull.Value;
                            cmdDetail.Parameters.Add("@TransDate", SqlDbType.DateTime).Value = (object)detail[i].TransDate ?? DBNull.Value;
                            cmdDetail.Transaction = txn;
                            cmdDetail.ExecuteNonQuery(); // Save Detail
                            cmdDetail.Dispose();

                            string strExecuteSp = "[dbo].[SP_GenPeriodicDuesKoreksi]";
                            SqlCommand cmdExecuteSp = new SqlCommand(strExecuteSp, conn);
                            cmdExecuteSp.CommandType = CommandType.StoredProcedure;
                            cmdExecuteSp.Parameters.Add("@pDebtorID", SqlDbType.VarChar).Value = domain.DebtorID;
                            cmdExecuteSp.Parameters.Add("@Param3", SqlDbType.VarChar).Value = domain.CreatedBy;
                            cmdExecuteSp.Parameters.Add("@YearRep", SqlDbType.VarChar).Value = domain.YearReport;
                            cmdExecuteSp.Parameters.Add("@param2", SqlDbType.VarChar).Value = string.Empty;
                            cmdExecuteSp.CommandTimeout = 0;
                            cmdExecuteSp.Transaction = txn;
                            cmdExecuteSp.ExecuteNonQuery();
                            cmdExecuteSp.Dispose();
                        }
                        
                        i = i + 1;
                    }

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }

            }
            return result;
        }

        public bool UpdateCorrection(recImposition domain, recImpositionDetail detail)
        {
            bool result = false;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlTransaction txn = conn.BeginTransaction();
                try
                {
                    string strDomain = "UPDATE transactions.recImposition SET DebtorValue = @DebtorValue, OJKValue = @OJKValue, ";
                    strDomain += " UpdatedDate = @UpdatedDate, UpdatedBy = @UpdatedBy WHERE ImpositionID = @ImpositionID AND YearReport = @YearReport AND DebtorSectorID = @DebtorSectorID AND SubSubSectorID = @SubSubSectorID ";
                    SqlCommand cmd = new SqlCommand(strDomain, conn);
                    cmd.Parameters.Add("@ImpositionID", SqlDbType.Int).Value = domain.ImpositionID;
                    cmd.Parameters.Add("@YearReport", SqlDbType.SmallInt).Value = domain.YearReport;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = domain.DebtorSectorID;
                    cmd.Parameters.Add("@DebtorValue", SqlDbType.Money).Value = (object)domain.DebtorValue ?? DBNull.Value;
                    cmd.Parameters.Add("@OJKValue", SqlDbType.Money).Value = (object)domain.OJKValue ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedDate", SqlDbType.DateTime).Value = (object)domain.UpdatedDate ?? DBNull.Value;
                    cmd.Parameters.Add("@UpdatedBy", SqlDbType.VarChar).Value = (object)domain.UpdatedBy ?? DBNull.Value;
                    cmd.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = (object)domain.SubSubSectorID ?? DBNull.Value;
                    cmd.Transaction = txn;
                    cmd.ExecuteNonQuery(); // Update Master
                    cmd.Dispose();

                    string strDetailUpdate = "UPDATE transactions.recImpositionDetail SET DebtorValue = @DebtorValue, OJKValue = @OJKValue, UpdateDate = @UpdateDate, UpdateBy = @UpdateBy WHERE ImpositionDetailID = @ImpositionDetailID AND ImpositionID = @ImpositionID ";
                    SqlCommand cmdDetailUpdate = new SqlCommand(strDetailUpdate, conn);
                    cmdDetailUpdate.Parameters.Add("@ImpositionDetailID", SqlDbType.Int).Value = detail.ImpositionDetailID;
                    cmdDetailUpdate.Parameters.Add("@ImpositionID", SqlDbType.Int).Value = domain.ImpositionID;
                    cmdDetailUpdate.Parameters.Add("@DebtorValue", SqlDbType.Money).Value = (object)detail.DebtorValue ?? DBNull.Value;
                    cmdDetailUpdate.Parameters.Add("@OJKValue", SqlDbType.Money).Value = (object)detail.OJKValue ?? DBNull.Value;
                    cmdDetailUpdate.Parameters.Add("@UpdateDate", SqlDbType.DateTime).Value = (object)detail.UpdateDate ?? DBNull.Value;
                    cmdDetailUpdate.Parameters.Add("@UpdateBy", SqlDbType.VarChar).Value = (object)detail.UpdateBy ?? DBNull.Value;
                    cmdDetailUpdate.Transaction = txn;
                    cmdDetailUpdate.ExecuteNonQuery(); // Update ImpositionDetail
                    cmdDetailUpdate.Dispose();

                    string strExecuteSp = "[dbo].[SP_GenPeriodicDuesKoreksi]";
                    SqlCommand cmdExecuteSp = new SqlCommand(strExecuteSp, conn);
                    cmdExecuteSp.CommandType = CommandType.StoredProcedure;
                    cmdExecuteSp.Parameters.Add("@pDebtorID", SqlDbType.VarChar).Value = domain.DebtorID;
                    cmdExecuteSp.Parameters.Add("@Param3", SqlDbType.VarChar).Value = domain.CreatedBy;
                    cmdExecuteSp.Parameters.Add("@YearRep", SqlDbType.VarChar).Value = domain.YearReport;
                    cmdExecuteSp.Parameters.Add("@param2", SqlDbType.VarChar).Value = string.Empty;
                    cmdExecuteSp.CommandTimeout = 0;
                    cmdExecuteSp.Transaction = txn;
                    cmdExecuteSp.ExecuteNonQuery();
                    cmdExecuteSp.Dispose();

                    result = true;
                    txn.Commit();
                }
                catch (Exception ex)
                {
                    txn.Rollback();
                    throw (ex);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }

            }
            return result;
        }

        public recImposition GetByYearReport(string yearReport, int debtorSectorId, int subsubSectorId)
        {
            recImposition t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT ImpositionID, YearReport, DebtorSectorID, DebtorValue, OJKValue, ImpositionBaseID, AttachedFileName, DirectoryLocation, TransTypeID, TariffID, Percentage, DebtorID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, Maximal, Minimal, TariffValue, IsFormula, Attachment, SubSubSectorID FROM transactions.recImposition WHERE YearReport = @YearReport AND SubSubSectorID = @SubSubSectorID AND DebtorSectorID = @DebtorSectorID";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@YearReport", SqlDbType.VarChar).Value = yearReport;
                    cmd.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = subsubSectorId;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = debtorSectorId;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recImposition();
                                t.ImpositionID = dr[recImposition.Fields.ImpositionID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.ImpositionID].ToString());
                                t.YearReport = dr[recImposition.Fields.YearReport] == DBNull.Value ? (short)0 : short.Parse(dr[recImposition.Fields.YearReport].ToString());
                                t.DebtorSectorID = dr[recImposition.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.DebtorSectorID].ToString());
                                t.DebtorValue = dr[recImposition.Fields.DebtorValue] == DBNull.Value ? 0 : decimal.Parse(dr[recImposition.Fields.DebtorValue].ToString());
                                t.OJKValue = dr[recImposition.Fields.OJKValue] == DBNull.Value ? 0 : decimal.Parse(dr[recImposition.Fields.OJKValue].ToString());
                                t.ImpositionBaseID = dr[recImposition.Fields.ImpositionBaseID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.ImpositionBaseID].ToString());
                                t.AttachedFileName = dr[recImposition.Fields.AttachedFileName] == DBNull.Value ? string.Empty : dr[recImposition.Fields.AttachedFileName].ToString();
                                t.DirectoryLocation = dr[recImposition.Fields.DirectoryLocation] == DBNull.Value ? string.Empty : dr[recImposition.Fields.DirectoryLocation].ToString();
                                t.TransTypeID = dr[recImposition.Fields.TransTypeID] == DBNull.Value ? (short?)null : short.Parse(dr[recImposition.Fields.TransTypeID].ToString());
                                t.TariffID = dr[recImposition.Fields.TariffID] == DBNull.Value ? (int?)null : int.Parse(dr[recImposition.Fields.TariffID].ToString());
                                t.Percentage = dr[recImposition.Fields.Percentage] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.Percentage].ToString());
                                t.DebtorID = dr[recImposition.Fields.DebtorID] == DBNull.Value ? (int?)null : int.Parse(dr[recImposition.Fields.DebtorID].ToString());
                                t.CreatedDate = dr[recImposition.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recImposition.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recImposition.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recImposition.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recImposition.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recImposition.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recImposition.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recImposition.Fields.UpdatedBy].ToString();
                                t.Maximal = dr[recImposition.Fields.Maximal] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.Maximal].ToString());
                                t.Minimal = dr[recImposition.Fields.Minimal] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.Minimal].ToString());
                                t.TariffValue = dr[recImposition.Fields.TariffValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.TariffValue].ToString());
                                t.IsFormula = dr[recImposition.Fields.IsFormula] == DBNull.Value ? (bool?)null : bool.Parse(dr[recImposition.Fields.IsFormula].ToString());
                                t.Attachment = dr[recImposition.Fields.Attachment] == DBNull.Value ? null : (byte[])(dr[recImposition.Fields.Attachment]);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

        public recImposition GetById(int impositionId, string yearReport, int debtorSectorId, int subsubSectorId)
        {
            recImposition t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" select tri.YearReport,tri.ImpositionID,trid.CalculationID,tri.DebtorID,tri.DebtorSectorID,tri.SubSubSectorID,trid.ImpositionDetailID ");
                    sbQuery.AppendFormat(" from transactions.recImposition tri ");
                    sbQuery.AppendFormat(" inner join transactions.recImpositionDetail trid ");
                    sbQuery.AppendFormat(" on trid.ImpositionID=tri.ImpositionID where tri.ImpositionID = @ImpositionID and trid.IsActive=1 ");
                    sbQuery.AppendFormat(" and tri.YearReport = @YearReport and tri.DebtorSectorID = @DebtorSectorID and tri.SubSubSectorID = @SubSubSectorID ");
                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@ImpositionID", SqlDbType.Int).Value = impositionId;
                    cmd.Parameters.Add("@YearReport", SqlDbType.VarChar).Value = yearReport;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = debtorSectorId;
                    cmd.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = subsubSectorId;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recImposition();
                                t.ImpositionID = dr[recImposition.Fields.ImpositionID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.ImpositionID].ToString());
                                t.YearReport = dr[recImposition.Fields.YearReport] == DBNull.Value ? (short)0 : short.Parse(dr[recImposition.Fields.YearReport].ToString());
                                t.DebtorSectorID = dr[recImposition.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.DebtorSectorID].ToString());
                                t.DebtorID = dr[recImposition.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.DebtorID].ToString());
                                t.SubSubSectorID = dr[recImposition.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.SubSubSectorID].ToString());
                                t.recImpositionDetail = new recImpositionDetail();
                                t.recImpositionDetail.CalculationID = dr[recImpositionDetail.Fields.CalculationID] == DBNull.Value ? 0 : int.Parse(dr[recImpositionDetail.Fields.CalculationID].ToString());
                                t.recImpositionDetail.ImpositionDetailID = dr[recImpositionDetail.Fields.ImpositionDetailID] == DBNull.Value ? 0 : int.Parse(dr[recImpositionDetail.Fields.ImpositionDetailID].ToString());
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

        public recImposition GetById(string yearReport, int debtorSectorId, int subsubSectorId)
        {
            recImposition t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat(" select tri.YearReport,tri.ImpositionID,trid.CalculationID,tri.DebtorID,tri.DebtorSectorID,tri.SubSubSectorID,trid.ImpositionDetailID ");
                    sbQuery.AppendFormat(" from transactions.recImposition tri ");
                    sbQuery.AppendFormat(" inner join transactions.recImpositionDetail trid ");
                    sbQuery.AppendFormat(" on trid.ImpositionID=tri.ImpositionID where trid.IsActive=1 ");
                    sbQuery.AppendFormat(" and tri.YearReport = @YearReport and tri.DebtorSectorID = @DebtorSectorID and tri.SubSubSectorID = @SubSubSectorID ");
                    cmd.CommandText = sbQuery.ToString();
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@YearReport", SqlDbType.VarChar).Value = yearReport;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = debtorSectorId;
                    cmd.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = subsubSectorId;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recImposition();
                                t.ImpositionID = dr[recImposition.Fields.ImpositionID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.ImpositionID].ToString());
                                t.YearReport = dr[recImposition.Fields.YearReport] == DBNull.Value ? (short)0 : short.Parse(dr[recImposition.Fields.YearReport].ToString());
                                t.DebtorSectorID = dr[recImposition.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.DebtorSectorID].ToString());
                                t.DebtorID = dr[recImposition.Fields.DebtorID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.DebtorID].ToString());
                                t.SubSubSectorID = dr[recImposition.Fields.SubSubSectorID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.SubSubSectorID].ToString());
                                t.recImpositionDetail = new recImpositionDetail();
                                t.recImpositionDetail.CalculationID = dr[recImpositionDetail.Fields.CalculationID] == DBNull.Value ? 0 : int.Parse(dr[recImpositionDetail.Fields.CalculationID].ToString());
                                t.recImpositionDetail.ImpositionDetailID = dr[recImpositionDetail.Fields.ImpositionDetailID] == DBNull.Value ? 0 : int.Parse(dr[recImpositionDetail.Fields.ImpositionDetailID].ToString());
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

        public recImposition GetByDebtorSectorId(int key)
        {
            recImposition t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT ImpositionID, YearReport, DebtorSectorID, DebtorValue, OJKValue, ImpositionBaseID, AttachedFileName, DirectoryLocation, TransTypeID, TariffID, Percentage, DebtorID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, Maximal, Minimal, TariffValue, IsFormula, Attachment, SubSubSectorID FROM transactions.recImposition WHERE DebtorSectorID = @DebtorSectorID";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = key;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recImposition();
                                t.ImpositionID = dr[recImposition.Fields.ImpositionID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.ImpositionID].ToString());
                                t.YearReport = dr[recImposition.Fields.YearReport] == DBNull.Value ? (short)0 : short.Parse(dr[recImposition.Fields.YearReport].ToString());
                                t.DebtorSectorID = dr[recImposition.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.DebtorSectorID].ToString());
                                t.DebtorValue = dr[recImposition.Fields.DebtorValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.DebtorValue].ToString());
                                t.OJKValue = dr[recImposition.Fields.OJKValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.OJKValue].ToString());
                                t.ImpositionBaseID = dr[recImposition.Fields.ImpositionBaseID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.ImpositionBaseID].ToString());
                                t.AttachedFileName = dr[recImposition.Fields.AttachedFileName] == DBNull.Value ? string.Empty : dr[recImposition.Fields.AttachedFileName].ToString();
                                t.DirectoryLocation = dr[recImposition.Fields.DirectoryLocation] == DBNull.Value ? string.Empty : dr[recImposition.Fields.DirectoryLocation].ToString();
                                t.TransTypeID = dr[recImposition.Fields.TransTypeID] == DBNull.Value ? (short?)null : short.Parse(dr[recImposition.Fields.TransTypeID].ToString());
                                t.TariffID = dr[recImposition.Fields.TariffID] == DBNull.Value ? (int?)null : int.Parse(dr[recImposition.Fields.TariffID].ToString());
                                t.Percentage = dr[recImposition.Fields.Percentage] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.Percentage].ToString());
                                t.DebtorID = dr[recImposition.Fields.DebtorID] == DBNull.Value ? (int?)null : int.Parse(dr[recImposition.Fields.DebtorID].ToString());
                                t.CreatedDate = dr[recImposition.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recImposition.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recImposition.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recImposition.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recImposition.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recImposition.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recImposition.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recImposition.Fields.UpdatedBy].ToString();
                                t.Maximal = dr[recImposition.Fields.Maximal] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.Maximal].ToString());
                                t.Minimal = dr[recImposition.Fields.Minimal] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.Minimal].ToString());
                                t.TariffValue = dr[recImposition.Fields.TariffValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.TariffValue].ToString());
                                t.IsFormula = dr[recImposition.Fields.IsFormula] == DBNull.Value ? (bool?)null : bool.Parse(dr[recImposition.Fields.IsFormula].ToString());
                                t.Attachment = dr[recImposition.Fields.Attachment] == DBNull.Value ? null : (byte[])(dr[recImposition.Fields.Attachment]);
                                t.SubSubSectorID = dr[recImposition.Fields.SubSubSectorID] == DBNull.Value ? (int?)null : int.Parse(dr[recImposition.Fields.SubSubSectorID].ToString());
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

        public recImposition GetByDebtorIdAndSubSubSectorId(int debtorid, int subsubsectorid)
        {
            recImposition t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT ImpositionID, YearReport, DebtorSectorID, DebtorValue, OJKValue, ImpositionBaseID, AttachedFileName, DirectoryLocation, TransTypeID, TariffID, Percentage, DebtorID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, Maximal, Minimal, TariffValue, IsFormula, Attachment, SubSubSectorID FROM transactions.recImposition WHERE DebtorID = @DebtorID and SubSubSectorID=@SubSubSectorID ";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@DebtorID", SqlDbType.Int).Value = debtorid;
                    cmd.Parameters.Add("@SubSubSectorID", SqlDbType.Int).Value = subsubsectorid;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recImposition();
                                t.ImpositionID = dr[recImposition.Fields.ImpositionID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.ImpositionID].ToString());
                                t.YearReport = dr[recImposition.Fields.YearReport] == DBNull.Value ? (short)0 : short.Parse(dr[recImposition.Fields.YearReport].ToString());
                                t.DebtorSectorID = dr[recImposition.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.DebtorSectorID].ToString());
                                t.DebtorValue = dr[recImposition.Fields.DebtorValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.DebtorValue].ToString());
                                t.OJKValue = dr[recImposition.Fields.OJKValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.OJKValue].ToString());
                                t.ImpositionBaseID = dr[recImposition.Fields.ImpositionBaseID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.ImpositionBaseID].ToString());
                                t.AttachedFileName = dr[recImposition.Fields.AttachedFileName] == DBNull.Value ? string.Empty : dr[recImposition.Fields.AttachedFileName].ToString();
                                t.DirectoryLocation = dr[recImposition.Fields.DirectoryLocation] == DBNull.Value ? string.Empty : dr[recImposition.Fields.DirectoryLocation].ToString();
                                t.TransTypeID = dr[recImposition.Fields.TransTypeID] == DBNull.Value ? (short?)null : short.Parse(dr[recImposition.Fields.TransTypeID].ToString());
                                t.TariffID = dr[recImposition.Fields.TariffID] == DBNull.Value ? (int?)null : int.Parse(dr[recImposition.Fields.TariffID].ToString());
                                t.Percentage = dr[recImposition.Fields.Percentage] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.Percentage].ToString());
                                t.DebtorID = dr[recImposition.Fields.DebtorID] == DBNull.Value ? (int?)null : int.Parse(dr[recImposition.Fields.DebtorID].ToString());
                                t.CreatedDate = dr[recImposition.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recImposition.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recImposition.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recImposition.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recImposition.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recImposition.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recImposition.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recImposition.Fields.UpdatedBy].ToString();
                                t.Maximal = dr[recImposition.Fields.Maximal] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.Maximal].ToString());
                                t.Minimal = dr[recImposition.Fields.Minimal] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.Minimal].ToString());
                                t.TariffValue = dr[recImposition.Fields.TariffValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.TariffValue].ToString());
                                t.IsFormula = dr[recImposition.Fields.IsFormula] == DBNull.Value ? (bool?)null : bool.Parse(dr[recImposition.Fields.IsFormula].ToString());
                                t.Attachment = dr[recImposition.Fields.Attachment] == DBNull.Value ? null : (byte[])(dr[recImposition.Fields.Attachment]);
                                t.SubSubSectorID = dr[recImposition.Fields.SubSubSectorID] == DBNull.Value ? (int?)null : int.Parse(dr[recImposition.Fields.SubSubSectorID].ToString());
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

        public recImposition GetByDebtorSectorId(int key, bool isActive)
        {
            recImposition t = null;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT transactions.recImposition.ImpositionID, YearReport, DebtorSectorID, transactions.recImposition.DebtorValue, transactions.recImposition.OJKValue, ImpositionBaseID, AttachedFileName, DirectoryLocation, TransTypeID, TariffID, Percentage, DebtorID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, Maximal, Minimal, TariffValue, IsFormula, Attachment, SubSubSectorID FROM transactions.recImposition inner join transactions.recImpositionDetail ";
                    cmd.CommandText += " on transactions.recImpositionDetail.ImpositionID=transactions.recImposition.ImpositionID where transactions.recImposition.DebtorSectorID=@DebtorSectorID and IsActive=@IsActive ";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = key;
                    cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = isActive;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                t = new recImposition();
                                t.ImpositionID = dr[recImposition.Fields.ImpositionID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.ImpositionID].ToString());
                                t.YearReport = dr[recImposition.Fields.YearReport] == DBNull.Value ? (short)0 : short.Parse(dr[recImposition.Fields.YearReport].ToString());
                                t.DebtorSectorID = dr[recImposition.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.DebtorSectorID].ToString());
                                t.DebtorValue = dr[recImposition.Fields.DebtorValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.DebtorValue].ToString());
                                t.OJKValue = dr[recImposition.Fields.OJKValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.OJKValue].ToString());
                                t.ImpositionBaseID = dr[recImposition.Fields.ImpositionBaseID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.ImpositionBaseID].ToString());
                                t.AttachedFileName = dr[recImposition.Fields.AttachedFileName] == DBNull.Value ? string.Empty : dr[recImposition.Fields.AttachedFileName].ToString();
                                t.DirectoryLocation = dr[recImposition.Fields.DirectoryLocation] == DBNull.Value ? string.Empty : dr[recImposition.Fields.DirectoryLocation].ToString();
                                t.TransTypeID = dr[recImposition.Fields.TransTypeID] == DBNull.Value ? (short?)null : short.Parse(dr[recImposition.Fields.TransTypeID].ToString());
                                t.TariffID = dr[recImposition.Fields.TariffID] == DBNull.Value ? (int?)null : int.Parse(dr[recImposition.Fields.TariffID].ToString());
                                t.Percentage = dr[recImposition.Fields.Percentage] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.Percentage].ToString());
                                t.DebtorID = dr[recImposition.Fields.DebtorID] == DBNull.Value ? (int?)null : int.Parse(dr[recImposition.Fields.DebtorID].ToString());
                                t.CreatedDate = dr[recImposition.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recImposition.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recImposition.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recImposition.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recImposition.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recImposition.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recImposition.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recImposition.Fields.UpdatedBy].ToString();
                                t.Maximal = dr[recImposition.Fields.Maximal] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.Maximal].ToString());
                                t.Minimal = dr[recImposition.Fields.Minimal] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.Minimal].ToString());
                                t.TariffValue = dr[recImposition.Fields.TariffValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.TariffValue].ToString());
                                t.IsFormula = dr[recImposition.Fields.IsFormula] == DBNull.Value ? (bool?)null : bool.Parse(dr[recImposition.Fields.IsFormula].ToString());
                                t.Attachment = dr[recImposition.Fields.Attachment] == DBNull.Value ? null : (byte[])(dr[recImposition.Fields.Attachment]);
                                t.SubSubSectorID = dr[recImposition.Fields.SubSubSectorID] == DBNull.Value ? (int?)null : int.Parse(dr[recImposition.Fields.SubSubSectorID].ToString());
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return t;
        }

        public List<recImposition> GetByListDebtorSectorId(int key)
        {
            List<recImposition> tt = new List<recImposition>();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "SELECT ImpositionID, YearReport, DebtorSectorID, DebtorValue, OJKValue, ImpositionBaseID, AttachedFileName, DirectoryLocation, TransTypeID, TariffID, Percentage, DebtorID, CreatedDate, CreatedBy, UpdatedDate, UpdatedBy, Maximal, Minimal, TariffValue, IsFormula, Attachment, SubSubSectorID FROM transactions.recImposition WHERE DebtorSectorID = @DebtorSectorID";
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@DebtorSectorID", SqlDbType.Int).Value = key;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            while (dr.Read())
                            {
                                recImposition t = new recImposition();
                                t.ImpositionID = dr[recImposition.Fields.ImpositionID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.ImpositionID].ToString());
                                t.YearReport = dr[recImposition.Fields.YearReport] == DBNull.Value ? (short)0 : short.Parse(dr[recImposition.Fields.YearReport].ToString());
                                t.DebtorSectorID = dr[recImposition.Fields.DebtorSectorID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.DebtorSectorID].ToString());
                                t.DebtorValue = dr[recImposition.Fields.DebtorValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.DebtorValue].ToString());
                                t.OJKValue = dr[recImposition.Fields.OJKValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.OJKValue].ToString());
                                t.ImpositionBaseID = dr[recImposition.Fields.ImpositionBaseID] == DBNull.Value ? 0 : int.Parse(dr[recImposition.Fields.ImpositionBaseID].ToString());
                                t.AttachedFileName = dr[recImposition.Fields.AttachedFileName] == DBNull.Value ? string.Empty : dr[recImposition.Fields.AttachedFileName].ToString();
                                t.DirectoryLocation = dr[recImposition.Fields.DirectoryLocation] == DBNull.Value ? string.Empty : dr[recImposition.Fields.DirectoryLocation].ToString();
                                t.TransTypeID = dr[recImposition.Fields.TransTypeID] == DBNull.Value ? (short?)null : short.Parse(dr[recImposition.Fields.TransTypeID].ToString());
                                t.TariffID = dr[recImposition.Fields.TariffID] == DBNull.Value ? (int?)null : int.Parse(dr[recImposition.Fields.TariffID].ToString());
                                t.Percentage = dr[recImposition.Fields.Percentage] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.Percentage].ToString());
                                t.DebtorID = dr[recImposition.Fields.DebtorID] == DBNull.Value ? (int?)null : int.Parse(dr[recImposition.Fields.DebtorID].ToString());
                                t.CreatedDate = dr[recImposition.Fields.CreatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recImposition.Fields.CreatedDate].ToString());
                                t.CreatedBy = dr[recImposition.Fields.CreatedBy] == DBNull.Value ? string.Empty : dr[recImposition.Fields.CreatedBy].ToString();
                                t.UpdatedDate = dr[recImposition.Fields.UpdatedDate] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr[recImposition.Fields.UpdatedDate].ToString());
                                t.UpdatedBy = dr[recImposition.Fields.UpdatedBy] == DBNull.Value ? string.Empty : dr[recImposition.Fields.UpdatedBy].ToString();
                                t.Maximal = dr[recImposition.Fields.Maximal] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.Maximal].ToString());
                                t.Minimal = dr[recImposition.Fields.Minimal] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.Minimal].ToString());
                                t.TariffValue = dr[recImposition.Fields.TariffValue] == DBNull.Value ? (decimal?)null : decimal.Parse(dr[recImposition.Fields.TariffValue].ToString());
                                t.IsFormula = dr[recImposition.Fields.IsFormula] == DBNull.Value ? (bool?)null : bool.Parse(dr[recImposition.Fields.IsFormula].ToString());
                                t.Attachment = dr[recImposition.Fields.Attachment] == DBNull.Value ? null : (byte[])(dr[recImposition.Fields.Attachment]);
                                t.SubSubSectorID = dr[recImposition.Fields.SubSubSectorID] == DBNull.Value ? (int?)null : int.Parse(dr[recImposition.Fields.SubSubSectorID].ToString());
                                tt.Add(t);
                                t.Dispose();
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }
            }
            return tt;
        }

        public DataTable DownloadTemplate()
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "[dbo].[Sp_Download_Template]";
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
            }

            return dt;


        }

    }
}
